/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package trialinventory2.svc;

import db_inventory.map.InventoryEntries;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import trialinventory2.to.ListTO;
import trialinventory2.to.ProductTO;

/**
 *
 * @author i1
 */
public class S1 {

    public static void save(List<ProductTO> tos, String who) {
        SessionFactory sf = db_inventory.db_inventory.getSessionFactory();
        Session ses = sf.openSession();
        Transaction tx = ses.beginTransaction();

        try {

            for (ProductTO to : tos) {

                InventoryEntries ie = new InventoryEntries();

                ie.setProductName(to.product);
                ie.setProductPrice(to.price);
                ie.setProductQuantity(to.getQuantity());
                ie.setProductTotal(to.getTotal());
                ie.setModified(new Date());
                ie.setWho(who);
                ie.setCreated(ie.getModified());
                ses.save(ie);
            }

            tx.commit();
        } catch (Exception e) {
            tx.rollback();
            throw new RuntimeException(e);
        } finally {
            ses.close();
        }
    }

    public static List<ListTO> getListOfEntry() {

        SessionFactory sf = db_inventory.db_inventory.getSessionFactory();
        Session ses = sf.openSession();
        String s0 = "select "
                + "  ie.product_name "
                + " ,ie.product_price "
                + " ,ie.product_quantity "
                + "from "
                + "  db_inventory.inventory_entries ie; ";


        try {
            List<ListTO> getListOfEntry = new ArrayList();

            Connection conn = ses.connection();
            PreparedStatement pstmt = conn.prepareStatement(s0);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {

                String product = rs.getString(1);
                double price = rs.getDouble(2);
                double quantity = rs.getDouble(3);


                ListTO to = new ListTO(product, price);
                to.setQuantity(quantity);
                getListOfEntry.add(to);
            }

            return getListOfEntry;

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            ses.close();
        }
    }

    public static void updateListTO(ListTO to) {
        SessionFactory sf = db_inventory.db_inventory.getSessionFactory();
        Session ses = sf.openSession();
        Transaction tx = ses.beginTransaction();
        String h0 = "from InventoryEntries ie "
                + "where "
                + "     ie.productName = :productName "
                + " and ie.productPrice = :productPrice ";

        try {

            InventoryEntries ie = (InventoryEntries) ses.createQuery(h0)
                    .setString("productName", to.product)
                    .setDouble("productPrice", to.price)
                    .uniqueResult();

            if (ie == null) {
                return;
            }

            ie.setProductQuantity(to.getQuantity());

            ses.update(ie);


            tx.commit();
        } catch (Exception e) {
            tx.rollback();
            throw new RuntimeException(e);
        } finally {
            ses.close();
        }
    }
}
